<html>
<head>
<title>MySqQL Query</title>
</head>
<body>
<?php
//replace variables with correct information for your MySQL server
$host="localhost";
$user="root";
$password="password";
//connects to the server
$con = mysql_connect($host,$user,$password);
if(!$con)
{
	die('Could not connect: ' . mysql_error());
}
//creates user studentsurvey
//select mysql database
mysql_select_db("mysql");
//delete user if already existing
mysql_query("DELETE FROM user WHERE user='studentsurvey'");
//insert user into user table list
$sql = "INSERT INTO user(host, user, password, select_priv, insert_priv, update_priv)
		VALUES('localhost', 'studentsurvey', PASSWORD('studentsurvey'), 'Y', 'Y', 'Y')";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
} 
else
{
	echo('studentsurvey user created<br>');
}
mysql_query("FLUSH PRIVILEGES");
//delete existing database
if(!mysql_query("DROP DATABASE IF EXISTS StudentSurvey"))
{
	die('Error deleting database: ' . mysql_error());
}
//create new database
if(!mysql_query("CREATE DATABASE StudentSurvey"))
{
	die('Error creating database: ' . mysql_error());
}
//select new database
if(!mysql_select_db("StudentSurvey"))
{
	die('Could not connect: ' . mysql_error());
}
//Create Users Table
$sql = "CREATE TABLE user
		(
		ID int NOT NULL AUTO_INCREMENT,
		PRIMARY KEY(ID),
		user_type_ID int,
		fName varchar(15),
		lName varchar(15),
		email varchar(40),
		password char(40)
		)";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
} 
else
{
	echo('User table created<br>');
}
//Create user_type table
$sql =  "CREATE TABLE user_type
		(
		ID int NOT NULL AUTO_INCREMENT,
		PRIMARY KEY(ID),
		type_name varchar(15)
		)";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
} 
else
{
	echo('User type table created<br>');
}		
//Create completed table
$sql = "CREATE TABLE completed
		(
		stud_ID int,
		survey_ID int
		)";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
} 
else
{
	echo('Completed table created<br>');
}
//Create survey table
$sql = "CREATE TABLE survey
		(
		ID int NOT NULL AUTO_INCREMENT,
		PRIMARY KEY(ID),
		instructor_ID int,
		survey_name varchar(40),
		course_name varchar(40)
		)";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
} 
else
{
	echo('Survey table created<br>');
}
//Create survey table
$sql = "CREATE TABLE question
		(
		ID int NOT NULL AUTO_INCREMENT,
		PRIMARY KEY(ID),
		survey_ID int,
		question_text varchar(200),
		question_sum int,
		question_count int
		)";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
} 
else
{
	echo('Question table created<br>');
}
//populate user_type table
$sql = "INSERT INTO user_type
		(type_name)
		VALUES ('student')";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
}
else
{
$sql = "INSERT INTO user_type
		(type_name)
		VALUES ('professor')";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
}
else
{
	echo('User type information inserted<br>');
}
}
$passhash = sha1('password');
$sql = "INSERT INTO user
		(user_type_ID, fname, lname, email, password)
		VALUES (1, 'Andrew', 'Murray', 'amurray4@my.athens.edu', '" .$passhash ."')";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
}
else
{
	echo('Andrews information inserted<br>');
}
$sql = "INSERT INTO user
		(user_type_ID, fname, lname, email, password)
		VALUES (1, 'Craig', 'Tipton', 'ctipton@my.athens.edu', '" .$passhash ."')";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
}
else
{
	echo('Craigs information inserted<br>');
}
$sql = "INSERT INTO user
		(user_type_ID, fname, lname, email, password)
		VALUES (1, 'Donna', 'Coats', 'dcoats@my.athens.edu', '" .$passhash ."')";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
}
else
{
	echo('Donnas information inserted<br>');
}
$sql = "INSERT INTO user
		(user_type_ID, fname, lname, email, password)
		VALUES (2, 'Adam', 'Lewis', 'Adam.Lewis@athens.edu', '" .$passhash ."')";
if(!mysql_query($sql))
{
	die('Error completing query: ' . mysql_error());
}
else
{
	echo('Dr Lewis information inserted<br>');
}

echo('SQL script executed successfully. You may now delete sql.php from the php folder.');
?>
